#!/usr/bin/python
# -*- coding: utf-8 -*-  
"""
@Project : hello 
@file : optimized_sql_query.py
@Author : sheen
@time : 2025/05/20 11:45
@func : 优化版SQL查询工具
"""

import logging
import time
import json
import oracledb
from typing import List, Dict, Any

import pandas as pd

from com.cn.for_cdc.common.cdc_conmons import oracle_adm_connections
from com.cn.for_cdc.common.log_helper import configure_logging


def execute_queries(conn: oracledb.Connection, sql_statements: Dict[int, str]) -> None:
    """执行SQL查询并记录性能指标"""
    for index, sql in sql_statements.items():
        logging.info(f"开始执行第{index}行SQL详细查询".center(100, '-'))
        logging.info(f"\n {sql}")
        try:
            start_sql = time.time()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                # 格式化输出结果
                columns = [col[0] for col in cursor.description]
                rows = [dict(zip(columns, row)) for row in cursor]
                data = pd.DataFrame(rows).head(50)
                logging.info('\n' + data.to_string(index=False) + '\n')
                elapsed = time.time() - start_sql
                logging.info(f"执行耗时: {elapsed:.2f}s")
        except oracledb.DatabaseError as e:
            logging.error(f"SQL执行失败: {e}\nSQL:\n{sql}")
            raise


def check_sql_queries(config: Dict[str, Any], sql_statements: Dict[int, str]) -> None:
    """通过连接池执行查询"""
    try:
        # 初始化连接池
        pool = oracledb.SessionPool(
            user=config["user"],
            password=config["password"],
            dsn=config["dsn"],
            min=2,
            max=10,
            increment=2,
            encoding="UTF-8"
        )
        with pool.acquire() as conn:
            execute_queries(conn, sql_statements)
    except Exception as e:
        logging.error(f"数据库连接失败: {e}")
        raise


def query_detail(query_sql_dict):
    """主函数"""
    configure_logging()
    config = oracle_adm_connections['icibe_pro']
    # config = oracle_siebel_connections['icibe_pro']
    logging.info("开始执行SQL查询...")
    logging.info(config)
    start = time.time()
    check_sql_queries(config, query_sql_dict)
    logging.info(f"总耗时: {time.time() - start:.2f}s")


if __name__ == "__main__":
    query_sql_dict = {
        6: "SELECT /*+ parallel(16) */sc.ROW_ID AS contact_id, NVL(ATTRIB_11,'N')\nFROM siebel.s_contact@SBL sc,siebel.S_CONTACT_X@SBL scx\nWHERE sc.BU_ID = '1-7V7X' AND sc.row_id = scx.par_row_id \nAND sc.LAST_UPD < sysdate -1 / 12\nMINUS \nSELECT /*+ parallel(16) */sbc.CONTACT_ID,NVL(PUSH_FLG,'N')\nFROM STG_B_CONTACT sbc",
        37: "SELECT row_id,END_DT FROM siebel.CX_PROMO_REF@sbl WHERE BU_ID = '1-7V7X' AND last_upd < sysdate -1 / 12\nMINUS \nSELECT PROMO_ID,PROMO_END_DT FROM STG_B_PROMO_HEAD WHERE BU_CODE = 'ICIBE'",
        46: "SELECT /*+ parallel(16) */slri.ROW_ID,slri.member_id\nFROM siebel.S_LOY_TXN@SBL slt,siebel.s_loy_acrl_itm@SBL slri\nWHERE slt.ROW_ID = slri.TXN_ID AND slt.BU_ID = '1-7V7X' AND slri.last_upd > TO_DATE('2025-05-06','YYYY-MM-DD') AND slri.created > TO_DATE('2022-06-01','YYYY-MM-DD') AND slt.created > TO_DATE('2022-06-01','YYYY-MM-DD') AND slri.last_upd < SYSDATE - 1\nMINUS\nSELECT /*+ parallel(16) */sbmp.ROW_ID, member_id FROM STG_B_MEM_POINT sbmp WHERE sbmp.BU_CODE = 'ICIBE'",
        47: "SELECT /*+ parallel(16) */slri.ROW_ID ,slri.member_id\nFROM siebel.S_LOY_TXN@SBL slt,siebel.s_loy_rdm_itm@SBL slri\nWHERE slt.ROW_ID = slri.TXN_ID AND slt.BU_ID = '1-7V7X' AND slri.last_upd > TO_DATE('2025-05-06','YYYY-MM-DD') AND slri.created > TO_DATE('2022-06-01','YYYY-MM-DD') AND slt.created > TO_DATE('2022-06-01','YYYY-MM-DD') AND slri.last_upd < SYSDATE - 1\nMINUS\nSELECT /*+ parallel(16) */sbmp.ROW_ID,member_id FROM STG_B_MEM_POINT sbmp WHERE sbmp.BU_CODE = 'ICIBE'",
        50: "SELECT /*+ parallel(16) */slri.ROW_ID, slt.comments\nFROM siebel.S_LOY_TXN@SBL slt,siebel.s_loy_acrl_itm@SBL slri\nWHERE slt.ROW_ID = slri.TXN_ID AND slt.BU_ID = '1-7V7X' AND slt.BU_ID = '1-7V7X' AND slri.last_upd > TO_DATE('2025-05-06','YYYY-MM-DD') AND slri.created > TO_DATE('2022-06-01','YYYY-MM-DD') AND slt.created > TO_DATE('2022-06-01','YYYY-MM-DD') AND slri.last_upd < SYSDATE - 1\nMINUS\nSELECT /*+ parallel(16) */sbmp.ROW_ID, sbmp.COMMENTS\nFROM STG_B_MEM_POINT sbmp WHERE sbmp.BU_CODE = 'ICIBE'",
        58: "SELECT /*+ parallel(32) */slt.row_id AS order_id, slt.loy_member_id AS member_id\nFROM siebel.S_ORDER@SBL slt\nWHERE slt.BU_ID = '1-7V7X' AND STATUS_CD = 'Submitted' AND slt.last_upd > TO_DATE('2025-05-06','YYYY-MM-DD') AND slt.created> TO_DATE('2022-06-01','YYYY-MM-DD') AND slt.last_upd < SYSDATE - 1\nminus\nSELECT /*+ parallel(32) */sbt.row_id AS order_id, member_id\nFROM STG_B_TRANSACTION sbt\nWHERE TXN_TYPE_NAME = 'Sale'"}





    query_detail(query_sql_dict)
